/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.*;
import java.util.Vector;

import Beans.NgonNgu;
import Beans.ThoiDiem;

/**
 *
 * @author Venus
 */
public class ThoiDiem_Model {

    public Vector LayDSTD(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTD), MaNN, TenTD, TuGio, TuPhut, DenGio, DenPhut, TinhTrang from thoidiem where MaNN = " + langid + " Order by MaTD desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.ThoiDiem td = new Beans.ThoiDiem();
                td.setMaTD(rss.getInt(1));
                td.setMaNN(rss.getInt(2));
                td.setTenTD(rss.getString(3));
                td.setTuGio(rss.getInt(4));
                td.setTuPhut(rss.getInt(5));
                td.setDenGio(rss.getInt(6));
                td.setDenPhut(rss.getInt(7));
                td.setTinhTrang(rss.getByte(8));
                v.add(td);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayDSTDPPV(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTD), MaNN, TenTD, TuGio, TuPhut, DenGio, DenPhut, TinhTrang from thoidiem where MaNN = " + langid + " and TinhTrang = 1 Order by MaTD desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.ThoiDiem td = new Beans.ThoiDiem();
                td.setMaTD(rss.getInt(1));
                td.setMaNN(rss.getInt(2));
                td.setTenTD(rss.getString(3));
                td.setTuGio(rss.getInt(4));
                td.setTuPhut(rss.getInt(5));
                td.setDenGio(rss.getInt(6));
                td.setDenPhut(rss.getInt(7));
                td.setTinhTrang(rss.getByte(8));
                v.add(td);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static int TaoMa() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(MaTD) from thoidiem";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean AddTime(int MaTD, String timename, String tugio, String tuphut, String dengio, String denphut) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            NgonNgu[] dsnn = NgonNgu_Model.DSNgonNgu();
            String x = "insert into thoidiem values ";
            for (int i = 0; i < dsnn.length; i++) {
                x = x + "(" + MaTD + ", " + dsnn[i].getMaNN() + ", '" + timename + "' , " + tugio + ", " + tuphut + ", " + dengio + ", " + denphut + ", 1),";
            }
            String query = x.substring(0, x.length() - 1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector LayTime(int MaTD) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from thoidiem where MaTD = " + MaTD;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.ThoiDiem td = new Beans.ThoiDiem();
                td.setMaTD(rss.getInt(1));
                td.setMaNN(rss.getInt(2));
                td.setTenTD(rss.getString(3));
                td.setTuGio(rss.getInt(4));
                td.setTuPhut(rss.getInt(5));
                td.setDenGio(rss.getInt(6));
                td.setDenPhut(rss.getInt(7));
                td.setTinhTrang(rss.getByte(8));
                v.add(td);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean Edit(String tenlkh, String tugio, String tuphut, String dengio, String denphut, String matd, String mann) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " Update thoidiem set TenTD = '" + tenlkh + "'  where MaTD = " + matd + " and MaNN = " + mann;
            String query1 = " Update thoidiem set TuGio = " + tugio + ", TuPhut = " + tuphut + ", DenGio = " + dengio + ", DenPhut = " + denphut + " where MaTD = " + matd + " ";
            int n = stmt.executeUpdate(query);
            stmt.executeUpdate(query1);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean UpdateStatusTime(String matd, String status) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update thoidiem set TinhTrang = " + status + " where MaTD = " + matd;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector Search(String tentd, String tinhtrang, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaTD), MaNN, TenTD, TuGio, TuPhut, DenGio, DenPhut, TinhTrang "
                    + " from thoidiem "
                    + " where MaNN = " + langid + " ";
            String[] arr = {tentd, tinhtrang};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentd)) {
                    query = query + " and TenTD like '%" + tentd + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tinhtrang)) {
                    query = query + " and TinhTrang = " + tinhtrang + "  ";
                }
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.ThoiDiem td = new Beans.ThoiDiem();
                td.setMaTD(rss.getInt(1));
                td.setMaNN(rss.getInt(2));
                td.setTenTD(rss.getString(3));
                td.setTuGio(rss.getInt(4));
                td.setTuPhut(rss.getInt(5));
                td.setDenGio(rss.getInt(6));
                td.setDenPhut(rss.getInt(7));
                td.setTinhTrang(rss.getByte(8));
                v.add(td);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenTD(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTD), MaNN, TenTD, TuGio, TuPhut, DenGio, DenPhut, TinhTrang from thoidiem where MaNN = " + langid + " Order by TenTD " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.ThoiDiem td = new Beans.ThoiDiem();
                td.setMaTD(rss.getInt(1));
                td.setMaNN(rss.getInt(2));
                td.setTenTD(rss.getString(3));
                td.setTuGio(rss.getInt(4));
                td.setTuPhut(rss.getInt(5));
                td.setDenGio(rss.getInt(6));
                td.setDenPhut(rss.getInt(7));
                td.setTinhTrang(rss.getByte(8));
                v.add(td);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTTTD(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTD), MaNN, TenTD, TuGio, TuPhut, DenGio, DenPhut, TinhTrang from thoidiem where MaNN = " + langid + " Order by TinhTrang " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.ThoiDiem td = new Beans.ThoiDiem();
                td.setMaTD(rss.getInt(1));
                td.setMaNN(rss.getInt(2));
                td.setTenTD(rss.getString(3));
                td.setTuGio(rss.getInt(4));
                td.setTuPhut(rss.getInt(5));
                td.setDenGio(rss.getInt(6));
                td.setDenPhut(rss.getInt(7));
                td.setTinhTrang(rss.getByte(8));
                v.add(td);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenTD(String tentd, String tinhtrang, int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaTD), MaNN, TenTD, TuGio, TuPhut, DenGio, DenPhut, TinhTrang "
                    + " from thoidiem "
                    + " where MaNN = " + langid + " ";
            String[] arr = {tentd, tinhtrang};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentd)) {
                    query = query + " and TenTD like '%" + tentd + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tinhtrang)) {
                    query = query + " and TinhTrang = " + tinhtrang + "  ";
                }
            }
            query = query + " Order by TenTD " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.ThoiDiem td = new Beans.ThoiDiem();
                td.setMaTD(rss.getInt(1));
                td.setMaNN(rss.getInt(2));
                td.setTenTD(rss.getString(3));
                td.setTuGio(rss.getInt(4));
                td.setTuPhut(rss.getInt(5));
                td.setDenGio(rss.getInt(6));
                td.setDenPhut(rss.getInt(7));
                td.setTinhTrang(rss.getByte(8));
                v.add(td);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTTTD(String tentd, String tinhtrang, int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaTD), MaNN, TenTD, TuGio, TuPhut, DenGio, DenPhut, TinhTrang "
                    + " from thoidiem "
                    + " where MaNN = " + langid + " ";
            String[] arr = {tentd, tinhtrang};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentd)) {
                    query = query + " and TenTD like '%" + tentd + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tinhtrang)) {
                    query = query + " and TinhTrang = " + tinhtrang + "  ";
                }
            }
            query = query + " Order by TinhTrang " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.ThoiDiem td = new Beans.ThoiDiem();
                td.setMaTD(rss.getInt(1));
                td.setMaNN(rss.getInt(2));
                td.setTenTD(rss.getString(3));
                td.setTuGio(rss.getInt(4));
                td.setTuPhut(rss.getInt(5));
                td.setDenGio(rss.getInt(6));
                td.setDenPhut(rss.getInt(7));
                td.setTinhTrang(rss.getByte(8));
                v.add(td);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static ThoiDiem[] DSThoiDiem(int matd, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        Vector dsnn = new Vector();// biáº¿n dsnn lÆ°u danh sÃ¡ch ngÃ´n ngá»¯ hiá»‡n cÃ³.
        String query = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            query = "Select * from thoidiem where MaTD != " + matd + " and MaNN = " + langid;
            rss = stmt.executeQuery(query);
            //rss = ConnectionPool.Select(query);
            while (rss.next()) {
                Beans.ThoiDiem td = new Beans.ThoiDiem();
                td.setMaTD(rss.getInt(1));
                td.setMaNN(rss.getInt(2));
                td.setTenTD(rss.getString(3));
                td.setTuGio(rss.getInt(4));
                td.setTuPhut(rss.getInt(5));
                td.setDenGio(rss.getInt(6));
                td.setDenPhut(rss.getInt(7));
                td.setTinhTrang(rss.getByte(8));
                dsnn.add(td);
            }
            stmt.close();
            rss.close();
            conn.close();
            return convertToArray(dsnn);
        } catch (Exception e) {
            e.printStackTrace();

            return null;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static ThoiDiem[] convertToArray(Vector v) {
        int n = v.size();
        ThoiDiem[] arr = new ThoiDiem[n];
        for (int i = 0; i < n; i++) {
            arr[i] = (ThoiDiem) v.get(i);
        }
        return arr;
    }

    public static String LayTenTD(int matd, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String tenkv = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenTD from thoidiem where MaTD = " + matd + " and MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                tenkv = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return tenkv;
    }

    public static String TimMaTD(int gio, int phut, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int matd = 1;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from thoidiem where TinhTrang = 1 and (tugio + tuphut/60) <= (" + gio + "+" + phut + "/60) and (dengio + denphut/60) >= (" + gio + "+" + phut + "/60) and mann=" + langid;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                matd = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return String.valueOf(matd);
    }

    public static boolean CheckTime(String tugio, String tuphut, String dengio, String denphut, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            int giotu = Integer.parseInt(tugio);
            int phuttu = Integer.parseInt(tuphut);
            int gioden = Integer.parseInt(dengio);
            int phutden = Integer.parseInt(denphut);
            int tu = (giotu * 60) + phuttu;
            int den = (gioden * 60) + phutden;
            String query = "select TuGio, TuPhut, DenGio, DenPhut from thoidiem where MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                int a = rss.getInt(1);
                int b = rss.getInt(2);
                int c = rss.getInt(3);
                int d = rss.getInt(4);
                int from = (a * 60) + b;
                int to = (c * 60) + d;
                if (from < tu && tu < to) {
                    n = n + 1;
                }
                if (from < den && den < to) {
                    n = n + 1;
                }
                if (tu == from) {
                    n = n + 1;
                }
                if (den == to) {
                    n = n + 1;
                }
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n != 0) {
                return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}
